package com.mexcad.db.connectors;

import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.Session;


import com.mexcad.db.dto.CatAcreedorStatus;
import com.mexcad.db.dto.CatDepartamento;
import com.mexcad.db.dto.CatEsquemaCredito;
import com.mexcad.db.dto.CatEsquemaCreditoAc;
import com.mexcad.db.dto.CatMoneda;
import com.mexcad.db.dto.CatPeriodoSolicitado;
import com.mexcad.db.dto.CatReporte;
import com.mexcad.db.dto.CatServicio;
import com.mexcad.db.dto.CatTipoCliente;
import com.mexcad.db.dto.CatTipoContrato;
import com.mexcad.db.dto.CatTipoSolicitud;
import com.mexcad.db.dto.CatTipoUsuario;
import com.mexcad.db.dto.CatValor;
import com.mexcad.db.dto.Cliente;
import com.mexcad.db.dto.Solicitud;
import com.mexcad.db.dto.Usuario;

import com.mexcad.db.utils.HibernateUtil;
import com.mexcad.utils.Constantes;

public class CatalogosConnector {

 
	public static final int GUARDAR=0;
	public static final int EDITAR=1;
	public static final int FIRMAR=2;
	private static Log logger = LogFactory.getLog(CatalogosConnector.class);
	//private List<BienesSolicitadosDetalle> listaDetalles;
	//private List<BienesSolCvePtales> listaCvesPtales;

	public CatalogosConnector(){
		logger.debug("Instanciating CatalogosConnector");
	}
	
	
	
	
/*	public static Afectaciones creaInstanciaAfectaciones(){
/		Afectaciones afect = new Afectaciones();
		return afect;
	}
	*/
	
	  
	
			
	public static String getTasaBasePorId(int id) {
		Session session = HibernateUtil.getSessionFactory().openSession();
		String resp=null;
		try{
			String query ="select descTasaBase FROM CatTasaBase where idTasaBase="+id;
			resp=(String)session.createQuery(query).uniqueResult();
			//System.out.println(lista);
			session.close();
		}catch(org.hibernate.exception.GenericJDBCException excepts){
			if(session!= null)
				session.close();
			
		}catch(Exception e){
			if(session!= null)
				session.close();
			
		} finally{
			if(session!= null && session.isOpen())
				session.close();
			
		}
		return resp;	
	}
	
	
	public static List<CatTipoCliente> getAllTipoClientes(){
		Session session = HibernateUtil.getSessionFactory().openSession();
		String query ="FROM CatTipoCliente";
		List <CatTipoCliente>lista=null;
		try{
			lista=session.createQuery(query).list();
			session.close();
		}catch(org.hibernate.exception.GenericJDBCException excepts){
			if(session!= null)
				session.close();
			
		}catch(Exception e){
			if(session!= null)
				session.close();
			
		} finally{
			if(session!= null && session.isOpen())
				session.close();
			
		}
		return lista;	
	}
	
	public static List<CatServicio> getAllServicios(){
			Session session = HibernateUtil.getSessionFactory().openSession();
			List <CatServicio>lista=null;
		try{
			String query ="FROM CatServicio";
			lista=session.createQuery(query).list();
			//System.out.println(lista);
			session.close();
		}catch(org.hibernate.exception.GenericJDBCException excepts){
			if(session!= null)
				session.close();
			
		}catch(Exception e){
			if(session!= null)
				session.close();
			
		} finally{
			if(session!= null && session.isOpen())
				session.close();
			
		}
		return lista;	
	}
	
	public static List<Usuario> getAllUsuarios(){
		Session session = HibernateUtil.getSessionFactory().openSession();
		List <Usuario>lista=null;
		try{	
			String query ="FROM Usuario";
			lista=session.createQuery(query).list();
			//System.out.println(lista);
			session.close();
		}catch(org.hibernate.exception.GenericJDBCException excepts){
			if(session!= null)
				session.close();
			
		}catch(Exception e){
			if(session!= null)
				session.close();
			
		} finally{
			if(session!= null && session.isOpen())
				session.close();
			
		}
		return lista;	
	}
	public static List<CatValor> getAllValores(){
		List <CatValor>lista=null;
		Session session = HibernateUtil.getSessionFactory().openSession();
		String query ="FROM CatValor";
		try{
			lista=session.createQuery(query).list();
			//System.out.println(lista);
			session.close();
		}catch(org.hibernate.exception.GenericJDBCException excepts){
			if(session!= null)
				session.close();
			
		}catch(Exception e){
			if(session!= null)
				session.close();
			
		} finally{
			if(session!= null && session.isOpen())
				session.close();
			
		}
		return lista;	
	}
	
	public static List<CatDepartamento> getAllDepartamentos(){
		Session session = HibernateUtil.getSessionFactory().openSession();
		String query ="FROM CatDepartamento";
		List <CatDepartamento>lista=null;
		
		try{	
			lista=session.createQuery(query).list();
			//System.out.println(lista);
			session.close();
		}catch(org.hibernate.exception.GenericJDBCException excepts){
			if(session!= null)
				session.close();
			
		}catch(Exception e){
			if(session!= null)
				session.close();
			
		} finally{
			if(session!= null && session.isOpen())
				session.close();
			
		}
		return lista;	
	}
	
	public static List<CatReporte> getAllReportes(){
		Session session = HibernateUtil.getSessionFactory().openSession();
		List <CatReporte>lista=null;
		try{
			String query ="FROM CatReporte";
			lista=session.createQuery(query).list();
			//System.out.println(lista);
			session.close();
		}catch(org.hibernate.exception.GenericJDBCException excepts){
			if(session!= null&& session.isOpen())
				session.close();
			
		}catch(Exception e){
			if(session!= null&& session.isOpen())
				session.close();
			
		} finally{
			if(session!= null && session.isOpen())
				session.close();
			
		}
		return lista;	
	}
	
	public static String getReporteById(int id) {
		Session session = HibernateUtil.getSessionFactory().openSession();
		String lista=null;
		try{
			String query ="select descReporte FROM CatReporte where idReporte="+id;
			lista=(String)session.createQuery(query).uniqueResult();
		
			session.close();
		}catch(org.hibernate.exception.GenericJDBCException excepts){
			if(session!= null&& session.isOpen())
				session.close();
			
		}catch(Exception e){
			if(session!= null&& session.isOpen())
				session.close();
			
		} finally{
			if(session!= null && session.isOpen())
				session.close();
			
		}

		return lista;	
	}
	
	public static List<Cliente> getAllNodosPadre() {
		Session session = HibernateUtil.getSessionFactory().openSession();
		String query ="FROM Cliente where hojaArbol = false order by nombreCliente";
			List <Cliente>lista=null;
		try{
			lista=session.createQuery(query).list();
			//System.out.println(lista);
			session.close();
		}catch(org.hibernate.exception.GenericJDBCException excepts){
			if(session!= null&& session.isOpen())
				session.close();
			
		}catch(Exception e){
			if(session!= null&& session.isOpen())
				session.close();
			
		} finally{
			if(session!= null && session.isOpen())
				session.close();
			
		}
		return lista;	
	}
	
	public static List<CatTipoUsuario> getAllTipoUsuarios() {
		Session session = HibernateUtil.getSessionFactory().openSession();
		String query ="FROM CatTipoUsuario";
		List <CatTipoUsuario>lista=null;
	try{
		lista=session.createQuery(query).list();
		//System.out.println(lista);
		session.close();
	}catch(org.hibernate.exception.GenericJDBCException excepts){
		if(session!= null&& session.isOpen())
			session.close();
		
	}catch(Exception e){
		if(session!= null&& session.isOpen())
			session.close();
		
	} finally{
		if(session!= null && session.isOpen())
			session.close();
		
	}
		return lista;	
	}
	public static List<CatTipoSolicitud> getAllTipoSolicitud() {
		Session session = HibernateUtil.getSessionFactory().openSession();
		String query ="FROM CatTipoSolicitud";
		List <CatTipoSolicitud>lista=null;
		try{
			lista=session.createQuery(query).list();
			//System.out.println(lista);
			session.close();
		}catch(org.hibernate.exception.GenericJDBCException excepts){
			if(session!= null&& session.isOpen())
				session.close();
			
		}catch(Exception e){
			if(session!= null&& session.isOpen())
				session.close();
			
		} finally{
			if(session!= null && session.isOpen())
				session.close();
			
		}
		return lista;	
	}

	
	public static String getSolicitudByid(int id) {
		Session session = HibernateUtil.getSessionFactory().openSession();
		String query ="select descTipoSolicitud FROM CatTipoSolicitud where idTipoSolicitud="+id;
		String lista=null;
		try{
			lista=(String)session.createQuery(query).uniqueResult();
		
			//System.out.println(lista);
			session.close();
		}catch(org.hibernate.exception.GenericJDBCException excepts){
			if(session!= null&& session.isOpen())
				session.close();
			
		}catch(Exception e){
			if(session!= null&& session.isOpen())
				session.close();
			
		} finally{
			if(session!= null && session.isOpen())
				session.close();
			
		}
		return lista;	
	}

	
	public static List<CatTipoContrato> getAllTipoContratos() {
		Session session = HibernateUtil.getSessionFactory().openSession();
		String query ="FROM CatTipoContrato";
		List <CatTipoContrato>lista=null;
		try{
			lista=session.createQuery(query).list();
			session.close();
		}catch(org.hibernate.exception.GenericJDBCException excepts){
			if(session!= null&& session.isOpen())
				session.close();
			
		}catch(Exception e){
			if(session!= null&& session.isOpen())
				session.close();
			
		} finally{
			if(session!= null && session.isOpen())
				session.close();
			
		}
		return lista;	
	}



	
	
	
	
	
	
	
	
	
	
	
	
	
	public static String getTipoContratoById(int id) {
		Session session = HibernateUtil.getSessionFactory().openSession();
		String query ="select descTipoContrato FROM CatTipoContrato where idTipoContrato="+id;
		String lista=null;
		try {
			lista=(String)session.createQuery(query).uniqueResult();
			session.close();
		}catch(org.hibernate.exception.GenericJDBCException excepts){
			if(session!= null&& session.isOpen())
				session.close();
			
		}catch(Exception e){
			if(session!= null&& session.isOpen())
				session.close();
			
		} finally{
			if(session!= null && session.isOpen())
				session.close();
			
		}
		return lista;	
	}
	
	
	
	
	
	
	public static String getEsquemaCreditoByid(int id){
		Session session = HibernateUtil.getSessionFactory().openSession();
		String query ="select descEsquemaCredito FROM CatEsquemaCredito where idEsquemaCredito="+id;
		String lista=null;
		try{	
			lista=(String)session.createQuery(query).uniqueResult();
			//System.out.println(lista);
			session.close();
		}catch(org.hibernate.exception.GenericJDBCException excepts){
			if(session!= null&& session.isOpen())
				session.close();
			
		}catch(Exception e){
			if(session!= null&& session.isOpen())
				session.close();
			
		} finally{
			if(session!= null && session.isOpen())
				session.close();
			
		}
		return lista;	
	}
	
	public static String getEsquemaCreditoAcByid(int id){
		Session session = HibernateUtil.getSessionFactory().openSession();
		String query ="select descEsquemaCreditoAc FROM CatEsquemaCreditoAc where idEsquemaCreditoAc="+id;
		String lista=null;
		try{
			lista=(String)session.createQuery(query).uniqueResult();
			session.close();
		}catch(org.hibernate.exception.GenericJDBCException excepts){
			if(session!= null&& session.isOpen())
				session.close();
			
		}catch(Exception e){
			if(session!= null&& session.isOpen())
				session.close();
			
		} finally{
			if(session!= null && session.isOpen())
				session.close();
			
		}
		return lista;	
	}
	
	public static List<CatEsquemaCreditoAc> getAllEsquemaCreditoAc() {
		Session session = HibernateUtil.getSessionFactory().openSession();
		String query ="FROM CatEsquemaCreditoAc";
		List <CatEsquemaCreditoAc>lista=null;
		try{
			lista=session.createQuery(query).list();
			session.close();
		}catch(org.hibernate.exception.GenericJDBCException excepts){
			if(session!= null&& session.isOpen())
				session.close();
			
		}catch(Exception e){
			if(session!= null&& session.isOpen())
				session.close();
			
		} finally{
			if(session!= null && session.isOpen())
				session.close();
			
		}
		return lista;	
	}
	
	public static List<CatMoneda> getAllMonedas(){
		Session session = HibernateUtil.getSessionFactory().openSession();
		String query ="FROM CatMoneda";
		List <CatMoneda>lista=null;
		try{	
			lista=session.createQuery(query).list();
			session.close();
		}catch(org.hibernate.exception.GenericJDBCException excepts){
			if(session!= null&& session.isOpen())
				session.close();
			
		}catch(Exception e){
			if(session!= null&& session.isOpen())
				session.close();
			
		} finally{
			if(session!= null && session.isOpen())
				session.close();
			
		}
		return lista;	
	}

	public static CatMoneda getMonedaById(int id){
		Session session = HibernateUtil.getSessionFactory().openSession();
		String query ="FROM CatMoneda where idMoneda="+id;
		CatMoneda moneda=null;
		try{
			moneda=(CatMoneda)session.createQuery(query).uniqueResult();
			session.close();
		}catch(org.hibernate.exception.GenericJDBCException excepts){
			if(session!= null&& session.isOpen())
				session.close();
			
		}catch(Exception e){
			if(session!= null&& session.isOpen())
				session.close();
			
		} finally{
			if(session!= null && session.isOpen())
				session.close();
			
		}
		return moneda;	
	}
	
	public static List<CatEsquemaCredito> getAllEsquemaCredito(){
		Session session = HibernateUtil.getSessionFactory().openSession();
		String query ="FROM CatEsquemaCredito order by idEsquemaCredito";
		List <CatEsquemaCredito>lista=null;
		try{
			lista=session.createQuery(query).list();
			//System.out.println(lista);
			session.close();
		}catch(org.hibernate.exception.GenericJDBCException excepts){
			if(session!= null&& session.isOpen())
				session.close();
			
		}catch(Exception e){
			if(session!= null&& session.isOpen())
				session.close();
			
		} finally{
			if(session!= null && session.isOpen())
				session.close();
			
		}
		return lista;	
	}
	
	public static List<CatPeriodoSolicitado> getAllPeriodoSolicitadoReport(){
		Session session = HibernateUtil.getSessionFactory().openSession();
		String query ="FROM CatPeriodoSolicitado  order by idPeriodoSolicitado ";
		List <CatPeriodoSolicitado>lista=null;
		try{
			lista=session.createQuery(query).list();
			//System.out.println(lista);
			session.close();
		}catch(org.hibernate.exception.GenericJDBCException excepts){
			if(session!= null&& session.isOpen())
				session.close();
			
		}catch(Exception e){
			if(session!= null&& session.isOpen())
				session.close();
			
		} finally{
			if(session!= null && session.isOpen())
				session.close();
			
		}
		return lista;	
	}
	
	public static List<CatPeriodoSolicitado> getAllPeriodoSolicitado(){
		Session session = HibernateUtil.getSessionFactory().openSession();
		String query ="FROM CatPeriodoSolicitado  WHERE idPeriodoSolicitado < 5 order by idPeriodoSolicitado";
		List <CatPeriodoSolicitado>lista=null;
		try{
			lista=session.createQuery(query).list();
			//System.out.println(lista);
			session.close();
		}catch(org.hibernate.exception.GenericJDBCException excepts){
			if(session!= null&& session.isOpen())
				session.close();
			
		}catch(Exception e){
			if(session!= null&& session.isOpen())
				session.close();
			
		} finally{
			if(session!= null && session.isOpen())
				session.close();
			
		}
		return lista;	
	}

	public static List<CatPeriodoSolicitado> getAllPeriodoSolicitado(int cliente){
		Session session = HibernateUtil.getSessionFactory().openSession();
		
		String query =null;
		if(cliente == SolicitudConnector.PSG &&( Constantes.SISTEMA.LINUX_SOFOM == Constantes.AMBIENTE || Constantes.SISTEMA.WINDOWS_SOFOM == Constantes.AMBIENTE )){
			query="FROM CatPeriodoSolicitado  WHERE idPeriodoSolicitado > 4 order by idPeriodoSolicitado";
		}else{
			query="FROM CatPeriodoSolicitado  WHERE idPeriodoSolicitado < 5 order by idPeriodoSolicitado";
		}
		List <CatPeriodoSolicitado>lista=null;
		try{
			lista=session.createQuery(query).list();
			//System.out.println(lista);
			session.close();
		}catch(org.hibernate.exception.GenericJDBCException excepts){
			if(session!= null&& session.isOpen())
				session.close();
			
		}catch(Exception e){
			if(session!= null&& session.isOpen())
				session.close();
			
		} finally{
			if(session!= null && session.isOpen())
				session.close();
			
		}
		return lista;	
	}

	
	public static void main(String []arg){
		
		//List <Cliente>lista2=ClienteConnector.getClientesPorEstado(1);
		System.out.println("parentCount: "+CatalogosConnector.getAllPeriodoSolicitadoReport().size());
		
		
		
	}




	public static String getPlazoSolicitadoPorId(int id) {
		Session session = HibernateUtil.getSessionFactory().openSession();
		String query ="select descPeriodoSolicitado FROM CatPeriodoSolicitado where idPeriodoSolicitado="+id;
		String lista=null;
		try{
			lista=(String)session.createQuery(query).uniqueResult();
			//System.out.println(lista);
			session.close();
		}catch(org.hibernate.exception.GenericJDBCException excepts){
			if(session!= null&& session.isOpen())
				session.close();
			
		}catch(Exception e){
			if(session!= null&& session.isOpen())
				session.close();
			
		} finally{
			if(session!= null && session.isOpen())
				session.close();
			
		}
		return lista;	
	}
	public static String getEstatusCreditoRentaPorId(int id) {
		Session session = HibernateUtil.getSessionFactory().openSession();
		String query ="select descEstatus FROM CatEstatus where idEstatus="+id;
		String lista=null;
		try{	
			lista=(String)session.createQuery(query).uniqueResult();
			//System.out.println(lista);
			session.close();
		}catch(org.hibernate.exception.GenericJDBCException excepts){
			if(session!= null&& session.isOpen())
				session.close();
			
		}catch(Exception e){
			if(session!= null&& session.isOpen())
				session.close();
			
		} finally{
			if(session!= null && session.isOpen())
				session.close();
			
		}
		return lista;	
	}


	public static String getEstatusPorId(int id) {
		Session session = HibernateUtil.getSessionFactory().openSession();
		String query ="select descEstatusSolicitud FROM CatEstatusSolicitud where idEstatusSolicitud="+id;
		String lista=null;
		try{
			lista=(String)session.createQuery(query).uniqueResult();
			//System.out.println(lista);
			session.close();
		}catch(org.hibernate.exception.GenericJDBCException excepts){
			if(session!= null&& session.isOpen())
				session.close();
			
		}catch(Exception e){
			if(session!= null&& session.isOpen())
				session.close();
			
		} finally{
			if(session!= null && session.isOpen())
				session.close();
			
		}
		return lista;	
	}

	public static Solicitud getSolicitudCompletaByid(int id){
		Session session = HibernateUtil.getSessionFactory().openSession();
		String query ="FROM Solicitud where cksolicitud.idSolicitud="+id;
		Solicitud sol=null;
		try{	
			sol=(Solicitud)session.createQuery(query).uniqueResult();
			session.close();
		}catch(org.hibernate.exception.GenericJDBCException excepts){
			if(session!= null&& session.isOpen())
				session.close();
			
		}catch(Exception e){
			if(session!= null&& session.isOpen())
				session.close();
			
		} finally{
			if(session!= null && session.isOpen())
				session.close();
			
		}
		return sol;	
	}

	public static String getAcreedorEstatusByid(int id){
		Session session = HibernateUtil.getSessionFactory().openSession();
		String query ="select descAcreedorstatus FROM CatAcreedorStatus where idEstatusCreditoAcc="+id;
		String sol=null;
		try{
			sol=(String)session.createQuery(query).uniqueResult();
			session.close();
		}catch(org.hibernate.exception.GenericJDBCException excepts){
			if(session!= null&& session.isOpen())
				session.close();
			
		}catch(Exception e){
			if(session!= null&& session.isOpen())
				session.close();
			
		} finally{
			if(session!= null && session.isOpen())
				session.close();
			
		}
		return sol;	
	}
	
	public static List<CatAcreedorStatus> getAllStatusAcreedorEstatus(){
		Session session = HibernateUtil.getSessionFactory().openSession();
		String query ="FROM CatAcreedorStatus ";
		List<CatAcreedorStatus> lista=null;
		try{
			lista=session.createQuery(query).list();
			session.close();
		}catch(org.hibernate.exception.GenericJDBCException excepts){
			if(session!= null&& session.isOpen())
				session.close();
			
		}catch(Exception e){
			if(session!= null&& session.isOpen())
				session.close();
			
		} finally{
			if(session!= null && session.isOpen())
				session.close();
			
		}
		return lista;	
	}
	

	
	
}
